using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using QuanLyThuVien1.DTO;
namespace QuanLyThuVien1.DAO
{
    public class CNHANVIEN_DAO
    {
        public DataTable layDSNhanVien()
        {
            DataTable m_dSNV = new DataTable();
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from NHANVIEN";
            OleDbDataAdapter oleDb = new OleDbDataAdapter(connecstr, connect);
            oleDb.Fill(m_dSNV);
            connect.Close();
            return m_dSNV;
        }
        public void xoaNhanVien(string maNV)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "delete from NHANVIEN where maNhanVien = " + maNV;
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.ExecuteNonQuery();
            connect.Close();
        }
        public void update( string maNV,string pass,NHANVIEN n)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "update NHANVIEN set  pass  = '" + n.PASSW + "',diaChi = '" + n.DIACHI +"',dienThoai = '" + n.DIENTHOAI +"',bangCap = '" + n.BANGCAP +"', ";
            connecstr += "boPhan = '"+n.BOPHAN+"',chucVu = '"+n.CHUCVU+"' where maNhanVien = '"+maNV+"' and pass = '"+pass+"' ";
            OleDbCommand command = new OleDbCommand(connecstr, connect);
            command.ExecuteNonQuery();
            connect.Close();
        }
        public void themNhanVien(NHANVIEN n)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "insert into NHANVIEN values('"+ n.MANHANVIEN +"','"+n.TENNHANVIEN+"','"+n.PASSW+"',";
            connecstr += "'"+n.DIACHI+"','"+ n.DIENTHOAI+"','"+n.NGAYSINH+"','" +n.PHAI+"','"+n.BOPHAN+"',";
            connecstr += "'"+n.CHUCVU+"','"+n.SOCMND+"','"+n.BANGCAP+"')";
            OleDbCommand comamnd = new OleDbCommand(connecstr, connect);
            comamnd.ExecuteNonQuery();
            connect.Close();
        }
        public string LayPassWord(string maNV)
        {
            string pass = null;
            OleDbConnection connect = KETNOI.taoKetNoi();
            string conncstr = "select pass from NHANVIEN where maNhanVien = " + maNV;
            OleDbCommand command = new OleDbCommand(conncstr, connect);
            OleDbDataReader Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                pass = Reader.GetString(0);
            }
            connect.Close();
            return pass;
       
        }
        public bool KiemTraMaNhanVien(string maNV)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from NHANVIEN where maNhanVien = " + maNV;
            DataTable d = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            adapter.Fill(d);
            connect.Close();
            if (d.Rows.Count > 0)
                return true;
            else return false;
        }
        public bool KiemTraPassNhanvien(string pass)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from NHANVIEN where pass = " + pass;
            DataTable d = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            adapter.Fill(d);
            connect.Close();
            if (d.Rows.Count > 0)
                return true;
            else return false;
        
       }
        public DataTable LayNhanVien(string pass, string MaNV)
        {
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from NHANVIEN where pass = '"+pass+"' and maNhanVien =  '"+ MaNV+"'";
            DataTable d = new DataTable();
            OleDbDataAdapter adapter = new OleDbDataAdapter(connecstr, connect);
            adapter.Fill(d);
            connect.Close();
            return d;
        }
        public NHANVIEN LayThongTinNhanVien(string maNV)
        {
            NHANVIEN n = new NHANVIEN();
            OleDbConnection connect = KETNOI.taoKetNoi();
            string connecstr = "select * from NHANVIEN where  maNhanVien =  '" + maNV + "'";
            OleDbCommand command = new OleDbCommand(connecstr,connect);
            OleDbDataReader Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                n.MANHANVIEN = Reader.GetString(0);
                n.TENNHANVIEN = Reader.GetString(1);
                n.PASSW = Reader.GetString(2);
                n.DIACHI = Reader.GetString(3);
                n.DIENTHOAI = Reader.GetString(4);
                n.NGAYSINH = Reader.GetDateTime(5);
                n.PHAI = Reader.GetString(6);
                n.BOPHAN = Reader.GetString(7);
                n.CHUCVU = Reader.GetString(8);
                n.SOCMND = Reader.GetInt32(9);
                n.BANGCAP = Reader.GetString(10);
            }

            connect.Close();
            return n;
        }
    }
}
